﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DuncanDAL
{
    public abstract class DBHelper
    {
        static DBHelper()
        {
            strConn = System.Configuration.ConfigurationManager.ConnectionStrings["testconstr"].ToString();
        }

        public static readonly string strConn;

        /// <summary>
        /// 查询数据库并填充一个DataTable
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="data">DataTable对象（必须已实例化）</param>
        public static void FillData(string _sql, DataTable _dataTable)
        {
            if (null == _dataTable)
                throw new NullReferenceException("数据库对象未实例化！");
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                OleDbDataAdapter adpter = new OleDbDataAdapter(_sql, cn);
                try
                {
                    if (cn.State != ConnectionState.Open)
                        cn.Open();

                    adpter.Fill(_dataTable);
                }
                catch (ArgumentException e)
                {
                    throw new ArgumentException("传入的sql语法错误！", _sql, e);
                }
                catch (SqlException e)
                {
                    throw new ArgumentException("数据库连接发生异常！", _sql, e);
                }
                finally
                {
                    if (cn.State != ConnectionState.Closed)
                        cn.Close();
                }
            }
        }

        /// <summary>
        /// 保存数据（表结构有自动递增）
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static int SaveDataWithIdentity(DataTable dataTable)
        {
            int count = 0;
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                if (cn.State != ConnectionState.Open)
                    cn.Open();
                OleDbCommand cmd = cn.CreateCommand();
                cmd.CommandText = "select * from " + dataTable.TableName;
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(ad);
                count = ad.Update(dataTable);
                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                }
            }

            return count;
        }

        /// <summary>
        /// 保存数据（表结构无自动递增）
        /// </summary>
        /// <remarks>适合小数据量</remarks>
        /// <param name="data"></param>
        public static int SaveData(DataTable data)
        {
            if (null == data)
                throw new ArgumentNullException("保存到数据库的DataTable对象不能为空！");
            if (string.IsNullOrEmpty(data.TableName))
                throw new ArgumentException("保存到数据库的DataTable对象没有表名！");
            if (null == data.PrimaryKey || data.PrimaryKey.Length != 1)
                throw new ArgumentException("保存到数据库的DataTable对象没有主键列！");

            int count = 0;  // 保存到数据库的记录数
            string primarykey = data.PrimaryKey[0].ColumnName;  // 主键列名

            // 生成 Insert、Update、Delete SQL语句
            string sqlInsert = "insert into " + data.TableName + " (";
            string sqlUpdate = "update " + data.TableName + " set ";
            string sqlDelete = "delete " + data.TableName;
            string sqlValues = string.Empty;

            foreach (DataColumn col in data.Columns)
            {
                sqlInsert += col.ColumnName + ",";
                sqlValues += "@" + col.ColumnName + ",";

                if (col.ColumnName != primarykey)
                    sqlUpdate += col.ColumnName + "=" + "@" + col.ColumnName + ",";
            }
            sqlInsert = sqlInsert.TrimEnd(',');  // 移除最后一个，
            sqlValues = sqlValues.TrimEnd(',');  // 移除最后一个，
            sqlUpdate = sqlUpdate.TrimEnd(',');  // 移除最后一个，

            sqlInsert += ") values (" + sqlValues + ")";
            sqlUpdate += " where (1=1)";
            sqlUpdate += " and " + primarykey + "=@" + primarykey;
            sqlDelete += " where (1=1)";
            sqlDelete += " and " + primarykey + "=@" + primarykey;

            OleDbConnection conn = new OleDbConnection(strConn);

            // 创建SqlCommand对象
            OleDbCommand cmdInsert = conn.CreateCommand();
            cmdInsert.CommandText = sqlInsert;
            OleDbCommand cmdUpdate = conn.CreateCommand();
            cmdUpdate.CommandText = sqlUpdate;
            OleDbCommand cmdDelete = conn.CreateCommand();
            cmdDelete.CommandText = sqlDelete;

            OleDbTransaction tran = conn.BeginTransaction();
            if (null != conn)
            {
                cmdInsert.Transaction = tran;
                cmdUpdate.Transaction = tran;
                cmdDelete.Transaction = tran;
            }

            foreach (DataColumn col in data.Columns)
            {
                cmdInsert.Parameters.Add(new SqlParameter(col.ColumnName, col.DataType));
                cmdUpdate.Parameters.Add(new SqlParameter(col.ColumnName, col.DataType));
            }
            cmdDelete.Parameters.Add(new SqlParameter(primarykey, data.PrimaryKey[0].DataType));

            // 如果连接未打开，打开连接
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();
            tran.Begin();
            // 保存数据到数据库
            foreach (DataRow row in data.Rows)
            {
                switch (row.RowState)
                {
                    case DataRowState.Added:
                        foreach (DataColumn col in data.Columns)
                        {
                            cmdInsert.Parameters[col.ColumnName].Value = row[col.ColumnName];
                        }
                        cmdInsert.ExecuteNonQuery();
                        row.AcceptChanges();
                        count++;
                        break;
                    case DataRowState.Modified:
                        foreach (DataColumn col in data.Columns)
                        {
                            cmdUpdate.Parameters[col.ColumnName].Value = row[col.ColumnName];
                        }
                        cmdUpdate.ExecuteNonQuery();
                        row.AcceptChanges();
                        count++;
                        break;
                    case DataRowState.Deleted:
                        row.RejectChanges();
                        cmdDelete.Parameters[primarykey].Value = row[primarykey];
                        cmdDelete.ExecuteNonQuery();
                        row.AcceptChanges();
                        count++;
                        break;
                }
            }
            tran.Commit();
            data.AcceptChanges();
            // 如果连接是自动打开的，使用连接完毕，关闭连接
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
                conn.Dispose();
            }

            return count;
        }

        /// <summary>
        /// 执行SQL语句（非查询语句）
        /// </summary>
        /// <remarks>这个方法不做任何检查，直接在数据库中执行，请谨慎。</remarks>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int Excute(string sql)
        {
            int result = 0;
            if (string.IsNullOrEmpty(sql))
                throw new ArgumentNullException("要执行的SQL语句不能为空！");
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                OleDbCommand cmd = cn.CreateCommand();
                cmd.CommandText = sql;

                // 如果连接未打开，打开连接
                if (cn.State != System.Data.ConnectionState.Open)
                    cn.Open();
                result = cmd.ExecuteNonQuery();

                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                    cn.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="sql">查询SQL</param>
        /// <returns>返回值</returns>
        public static object ExecuteScalar(string sql)
        {
            object ret = null;
            if (string.IsNullOrEmpty(sql))
                throw new ArgumentNullException("要执行的SQL语句不能为空！");
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                OleDbCommand cmd = cn.CreateCommand();
                cmd.CommandText = sql;

                // 如果连接未打开，打开连接
                if (cn.State != System.Data.ConnectionState.Open)
                    cn.Open();

                ret = cmd.ExecuteScalar();

                // 如果连接是自动打开的，使用连接完毕，关闭连接
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }
            return ret;
        }

        /// <summary>
        /// 执行INSERT SQL语句，并返回新主键值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExcuteInsert(string sql)
        {
            object ret = null;
            if (string.IsNullOrEmpty(sql))
                throw new ArgumentNullException("要执行的SQL语句不能为空！");
            using (OleDbConnection cn = new OleDbConnection(strConn))
            {
                OleDbCommand cmd = cn.CreateCommand();
                cmd.CommandText = sql;


                // 如果连接未打开，打开连接
                if (cn.State != System.Data.ConnectionState.Open)
                    cn.Open();

                cmd.CommandText += " select @@identity";
                ret = cmd.ExecuteScalar();

                // 如果连接是自动打开的，使用连接完毕，关闭连接
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }
            return ret;
        }
    }
}
